﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.IO





Public Class Reorder

    Private dataAdapter As New OleDbDataAdapter
    'Private bindingSource1 As New BindingSource()

    Private Sub Reorder_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.ReorderSize = Me.Size
        My.Settings.ReoderPos = Me.Location
        GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\reorderGW01.xml")

    End Sub

    Private Sub Reorder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Me.Size = My.Settings.ReorderSize
        FormPos(My.Settings.ReoderPos)
        Me.Location = My.Settings.ReoderPos

        If File.Exists(My.Settings.ConfigPath & "\reorderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\reorderGW01.xml")

    End Sub

    Function GetData(ByVal selectCommand As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapter = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table

            ' Resize the GridView columns to fit the newly loaded content.
            Me.GridView1.BestFitColumns()

        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim q As String
        q = ControlChars.Quote

        Me.TextBox1.Text = Me.TextBox1.Text.ToUpper
        Application.DoEvents()


        GetData("select MBWHLO as Stock , MBITNO, MMITDS as Description, MBSUWH as fromStock, MBREOP as ""MBREOP: ReorderPoint"", MBOPLC as ""MBOPLC: Planning Method"", MBSTQT ""MBSTQT: Bestand"", " & _
                "MBORQT , MBREQT , MBSSQT , MBPUIT  " & _
                "from Mitbal join mitmas on mbitno = mmitno " & _
                "where MBWHLO >= '" & Me.TextBox1.Text.ToString & "' and MBWHLO <= '" & Me.TextBox1.Text.ToString & "' " & _
                "order by MBWHLO, MBORQT DESC, MBITNO ")

        If File.Exists(My.Settings.ConfigPath & "\reorderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\reorderGW01.xml")

    End Sub



    Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then

            Dim q As String
            q = ControlChars.Quote
            Me.TextBox1.Text = Me.TextBox1.Text.ToUpper
            Application.DoEvents()

            GetData("select MBWHLO as Stock , MBITNO, MMITDS as Description, MBSUWH as fromStock, MBREOP as ""MBREOP: ReorderPoint"", MBOPLC as ""MBOPLC: Planning Method"", MBSTQT ""MBSTQT: Bestand"", " & _
                    "MBORQT , MBREQT , MBSSQT , MBPUIT  " & _
                    "from Mitbal join mitmas on mbitno = mmitno " & _
                    "where MBWHLO >= '" & Me.TextBox1.Text.ToString & "' and MBWHLO <= '" & Me.TextBox1.Text.ToString & "' " & _
                    "order by MBWHLO, MBORQT DESC, MBITNO ")
            If File.Exists(My.Settings.ConfigPath & "\reorderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\reorderGW01.xml")
        End If


    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick

        Dim q As String
        q = ControlChars.Quote
        Me.TextBox1.Text = Me.TextBox1.Text.ToUpper
        Application.DoEvents()

        GetData("select MBWHLO as Stock , MBITNO, MMITDS as Description, MBSUWH as fromStock, MBREOP as ""MBREOP: ReorderPoint"", MBOPLC as ""MBOPLC: Planning Method"", MBSTQT ""MBSTQT: Bestand"", " & _
                "MBORQT , MBREQT , MBSSQT , MBPUIT  " & _
                "from Mitbal join mitmas on mbitno = mmitno " & _
                "where MBWHLO >= '" & Me.TextBox1.Text.ToString & "' and MBWHLO <= '" & Me.TextBox1.Text.ToString & "' " & _
                "order by MBWHLO, MBORQT DESC, MBITNO ")

        If File.Exists(My.Settings.ConfigPath & "\reorderGW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\reorderGW01.xml")

    End Sub

    Private Sub BarButtonItem3_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_Reorder_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub
End Class